import pymysql
import pymongo
import random

# 连接MySQL数据库
mysql_conn = pymysql.connect(host='localhost',
                             user='root',
                             password='123456',
                             database='datavisible',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

# 连接MongoDB数据库
mongo_client = pymongo.MongoClient("mongodb://127.0.0.1:27017/")
mongo_db = mongo_client["recommender"]
mongo_collection = mongo_db["Product"]

# 清空集合数据
# mongo_collection.delete_many({})

print("集合数据已清空！")

# 查询MySQL数据
mysql_cursor = mysql_conn.cursor()
mysql_cursor.execute("""
SELECT id, title, image_src, location, cate
FROM (
    SELECT id, title, image_src, location, cate,
           ROW_NUMBER() OVER (PARTITION BY cate ORDER BY id) AS row_num
    FROM datavisible.goods
) AS subquery
WHERE row_num <= 100""")
mysql_data = mysql_cursor.fetchall()

# 很有启发|好看|不错|内容丰富|到货速度快|外观漂亮|好用|质量好|有破损|用起来很舒服
# 定义评价标签列表
evaluation_tags = ['很有启发', '好看', '不错', '内容丰富', '到货速度快', '外观漂亮', '好用', '质量好', '有破损', '用起来很舒服']

# 转换字段并插入到MongoDB
for row in mysql_data:
    # 将location字段转换为categories格式
    location_parts = row['location'].split(' ')
    categories = '|'.join(location_parts)
    # 随机选择评价标签并组合
    random_tags = random.sample(evaluation_tags, random.randint(1, len(evaluation_tags)))
    tags_combined = '|'.join(random_tags)

    # 构建MongoDB文档
    mongo_doc = {
        'productId': row['id'],
        'name': row['title'],
        'imageUrl': row['image_src'],
        'categories': row['cate'],
        'tags': tags_combined
    }

    # 插入文档到MongoDB
    mongo_collection.insert_one(mongo_doc)

# 关闭连接
mysql_cursor.close()
mysql_conn.close()
mongo_client.close()
